<?PHP
#
# Author: Cody Sorgenfrey
# copyright South Hill Calvary Chapel 2012
#

function concat_not ($a) {
	for ($i=0; $i < (count($a[2])-1); $i++) {
		$A .= $a[1]."!='".$a[2][$i]."' AND ";
	}
	$A .= $a[1]."!='".$a[2][$i]."' ";
	return $A;
}

function sermon_search_criteria() {
	global $bible_books;
	global $aService, $aName, $aType, $aType_change;
	global $book, $service, $name, $type, $date, $Title;
	printf("<form name='input' action='sermon.php' method='get'>\n");
	printf("<input type='text' name='title' placeholder='Title Search' value='%s'>\n", $Title);
	printf("<br/>\n");
	printf("<select name='Book'>\n");
	printf("<option value='All'>All Books</option>\n");
	for ($i=1; $i<=66; $i++) {
		if ($bible_books[$i][0] === $book) {
			printf("<option value='%s' selected='yes'>%s</option>\n", $bible_books[$i][0], $bible_books[$i][0]);
		} else {
			printf("<option value='%s'>%s</option>\n", $bible_books[$i][0], $bible_books[$i][0]);
		}
	}
	printf("</select>\n");
	printf("<br><select name='service'>\n");
	printf("<option value='All'>All Services</option>\n");
	for ($i=0; $i < count($aService); $i++) {
		if ($aService[$i] === $service) {
			printf("<option value='%s' selected='yes'>%s</option>\n", $aService[$i], $aService[$i]);
		} else {
			printf("<option value='%s'>%s</option>\n", $aService[$i], $aService[$i]);
		}
	}
	printf("</select>\n");
	printf("</br><select name='name'>\n");
	printf("<option value='All'>All Teachers</option>\n");
	for ($i=0; $i < count($aName); $i++) {
		if ($aName[$i] === $name) {
			printf("<option value='%s' selected='yes'>%s</option>\n", $aName[$i], add_title($aName[$i]));
		} else {
			printf("<option value='%s'>%s</option>\n", $aName[$i], add_title($aName[$i]));
		}
	}
	printf("</select>\n");
	printf("</br><select name='Type'>\n");
	printf("<option value='All'>All Studies</option>\n");
	for ($i=0; $i < count($aType); $i++) {
		if ($aType[$i] === $type) {
			printf("<option value='%s' selected='yes'>%s</option>\n", $aType[$i], $aType_change[$i]);
		} else {
			printf("<option value='%s'>%s</option>\n", $aType[$i], $aType_change[$i]);
		}
	}
	printf("</select>\n");
	printf("</br><select name='date'>\n");
	printf("<option value='All'>All Dates</option>\n");
	for ($y=2001; $y <= date("Y"); $y++) {
		if ($y == $date) {
			printf("<option value='%d' selected='yes'>%d</option>\n", $y, $y);
		} else {
			printf("<option value='%d'>%d</option>\n", $y, $y);
		}
	}
	printf("</select>\n");
	printf("<br/><br/>\n");
	printf("<input type='submit' value='Submit' /></p>\n");
	printf("</form>\n");
}
include_once("common.php");
include_once("../mysql/common.php");
include_once("../mysql/database_info.php");
$title = "Archived Messages";
$meta_key= "Church, Puyallup, SHCC, South Hill, Calvary Chapel, South Hill Calvary Chapel";
$meta_desc= "Teachings at South Hill Calvary Chapel.";
$morehtml=null;
$morejava = null;
startPage($title, $meta_key, $meta_desc, $morehtml, $morejava);
$book = $_GET['Book'];
$service = $_GET['service'];
$name = $_GET['name'];
$type = $_GET['Type'];
$date = $_GET['date'];
$Title = $_GET['title'];
$aService = array("Sunday", "Saturday", "Wednesday", "Retreat", "Breakfast", "Other");
$aName = array("Ron Sanchez","Frank Boulet","Alan Chappell","Charles Gillaspie","Jason Murdock","Jason Sanchez","Phil Spagnolo","Jason Strayer","Other");
$aType = array("Expositional", "Topical", "Holiday", "Mans", "Womens", "Marriage", "Other");
$aType_change = array("Verse by Verse", "In Depth Topical", "Holiday", "For Men", "For Women", "Marriage", "Other");
$param[0][0]=$book;
$param[0][1]='book';
$param[1][0] = $name;
$param[1][1]='name';
$param[1][2]=$aName;
$param[2][0] = $service;
$param[2][1]='service';
$param[2][2]=$aService;
$param[3][0] = $type;
$param[3][1]='Type';
$param[3][2]=$aType;
$param[4][0] = $date; 
$param[4][1] = 'date';
$param[5][0] = $Title; 
$param[5][1] = 'title';
$table = "sermons";
connect_select_test($table);
$num_params=0;
$all = false;
for ($i = 0; $i < count($param); $i++) {
	if (empty($param[$i][0])) {
		continue;
	}
	if ($param[$i][0] === 'All') {
		$all = true;
		continue;
	}
	$num_params++;
}
if ($num_params == 0 && $all) {
	sermon_search_criteria();
	$query = 'SELECT * FROM '.$table.' ORDER BY date DESC';
	$result=mysql_query($query) or die (mysql_error());
	$num_rows=mysql_num_rows($result);
	msermon_table($result,$num_rows,$table);
} else if ($num_params==0) {
	printf("<div>\n");
	printf("<p>Welcome to our archived sermons page.<br/>\n");
	printf("Please select the sermons to which you would like to listen by choosing from the options below.<br/>\n");
	printf("If you press 'Submit' with no criteria you will see all the messages.<br/>\n");
	printf("The Lord bless you as you take in His Word.</p>\n");
	printf("</div>\n");
	sermon_search_criteria();
} else {
	$query='SELECT * FROM '.$table.' WHERE ';
	$count=0;
	for ($i = 0; $i < count($param); $i++) {
		if (empty($param[$i][0]) || ($param[$i][0] === 'All')) {
			continue;
		}
		$count++;
		if ($count == $num_params) {
			if ($param[$i][1] === 'date') {
				$query=$query.$param[$i][1]." between '".$param[$i][0]."-01-01 00:00:00' and '".($param[$i][0]+1)."-01-01 00:00:00' ";
			}else if ($param[$i][1] === 'title') {
				$query=$query.$param[$i][1]." LIKE '%".$param[$i][0]."%' ";
			} else if ($param[$i][0] === 'Other') {
				$query=$query . concat_not($param[$i]);
			} else {
				$query=$query.$param[$i][1]." LIKE '%".$param[$i][0]."%' ";
			}
			break;
		}
		if ($param[$i][1] === 'date') {
			$query=$query.$param[$i][1]." between '".$param[$i][0]."-01-01 00:00:00' and '".($param[$i][0]+1)."-01-01 00:00:00' AND ";
		}else if ($param[$i][1] === 'title') {
			$query=$query.$param[$i][1]." LIKE '%".$param[$i][0]."%' AND ";
		} else if ($param[$i][0] === 'Other') {
			$query=$query . concat_not($param[$i]) . " AND ";
		} else {
			$query=$query.$param[$i][1]." LIKE '%".$param[$i][0]."%' AND ";
		}
	}
	sermon_search_criteria();
	$query = $query."ORDER BY date DESC";
	//printf("%s<br/>\n", $query);
	$result=mysql_query($query) or die (mysql_error());
	$num_rows=mysql_num_rows($result);
	if ($num_rows == 0) {
		printf("<div>\n");
		printf("<p>Sorry there are no sermons to display for this search.</p>\n");
		printf("</div>\n");
	} else {
		msermon_table($result,$num_rows,$table);
	}
}
endPage();

?>